﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using DreamBuilder.UI.UserControls.DataAccess.Services;
using System.Data;
using DreamBuilder.DictionaryAccess;
using DreamBuilder.Core.DAO;


namespace DreamBuilder.UI.UserControls.DataAccess.Components
{
    public class MsSqlUCChildDAL : UCChildDAL
    {
        public override string UCChildDBHelperString { get; set; }
        public override DataTable GetChildDataTable(string childEditID, string parentValue)
        {
            string getChildDataSql = GenerateGetDataSql(childEditID, parentValue);
            DataTable dtInfo = new DataTable();
            if (!string.IsNullOrEmpty(UCChildDBHelperString))
            {
                IDBHelper dbHelper = DBHelperFactory.CreateInstance(UCChildDBHelperString);
                dtInfo = dbHelper.ExecuteDataSet(getChildDataSql).Tables[0];
            }
            else
            {
                dtInfo = DBHelper.ExecuteDataSet(getChildDataSql).Tables[0];
            }
            SetPKFieldToDataTable(childEditID,dtInfo);
            return dtInfo;
        }
        private void SetPKFieldToDataTable(string childEditID,DataTable childDataTable)
        {
            string pkField = UChildCommon.GetChildTablePKField(childEditID);
            childDataTable.PrimaryKey = new DataColumn[] { childDataTable.Columns[pkField] };
        }

        private string GenerateGetDataSql(string childEditID, string parentValue)
        {
            System.Collections.Generic.List<ChildTableItemInfo> childitemList = DictionaryAccessFacade.GetSortedChildTableItemsInfo(childEditID);
            ChildEditTotalInfo pChildEditTotalInfo = DictionaryAccessFacade.GetChildEditTotalInfo(childEditID);
            string childTableCode = pChildEditTotalInfo.TableCode;

            string sql = "select ";
            string selectSql = "";
            string fromSql = " from " + childTableCode;
            string tablefieldCode = "";

            foreach (ChildTableItemInfo childItemInfo in childitemList)
            {
                tablefieldCode = childTableCode + "." + childItemInfo.FieldCode;
                selectSql += tablefieldCode + ",";

                if (childItemInfo.ItemType == EDITITEMTYPE.TYPE_ComboBoxBool 
                    || childItemInfo.ItemType == EDITITEMTYPE.TYPE_RadioBool)
                {
                    selectSql += "(case " + tablefieldCode + " when 1 then 'Yes' else 'No' end) as '" + childTableCode + "__" + childItemInfo.FieldCode + "',";
                }
                else if (childItemInfo.ItemType == EDITITEMTYPE.TYPE_Reference
                || childItemInfo.ItemType == EDITITEMTYPE.TYPE_ComboBoxReference)
                {

                    FieldDicInfo pFieldDicInfo = DictionaryAccessFacade.GetFieldDicInfo(childTableCode, childItemInfo.FieldCode);
                    UCControlCommon.GetRefSelectAndFromSql(pFieldDicInfo, ref fromSql, ref selectSql);
                }
                else
                {
                    selectSql += tablefieldCode + " as '" + childTableCode + "__" + childItemInfo.FieldCode + "',";
                }
            }

            selectSql += "'2' as RowStatus" + ",'Ordinary' as RowAttribute";
            string whereSql = "";
            whereSql += " where ( " + childTableCode + "." + UChildCommon.GetChildTableFKField(childEditID) + " = '" + parentValue + "' )";

            if (!string.IsNullOrEmpty(pChildEditTotalInfo.RefRangeSQL))
            {
                whereSql += " and ( " + pChildEditTotalInfo.RefRangeSQL + " ) ";
            }

            string orderSql = string.IsNullOrEmpty(pChildEditTotalInfo.OrderSql)?"":" Order By " + childTableCode + "." + pChildEditTotalInfo.OrderSql.Replace(",", "," + childTableCode + ".");
            return sql += selectSql + fromSql + whereSql + orderSql;
        }
    }
}
